<?php

class Application_Model_Natives {

    public function obrir_bd() {
        $link = mysql_connect('localhost', 'root', '')
                or die("Sorry - unable to connect to MySQL");
        $db_selected = mysql_select_db('pract_uf3', $link);
        mysql_query("SET NAMES 'utf8'");
        if (!$db_selected) {
            echo ('Error:' . mysql_error());
        }
        return $link;
    }

    public function tancar_bd($link) {
        mysql_close($link);
    }

    /*     * ******************************* DEPARTAMENTS *********************************** */

    public function getDept($id_dept) {
        $link = $this->obrir_bd();
        $sql = "SELECT * FROM dept where id='$id_dept'";
        $result = mysql_query($sql);
        $this->tancar_bd($link);
        return mysql_fetch_assoc($result);
    }

    public function addDept($n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO dept (nom,descripcio) VALUES ('$n_dept','$d_dept')";
        if (!mysql_query($sql, $link)) {
            die('Error: ' . mysql_error());
        }
        $this->tancar_bd($link);
    }

    public function editDept($id_dept, $n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "UPDATE dept SET nom = '$n_dept' , descripcio = '$d_dept' WHERE id='$id_dept'";
        if (!mysql_query($sql, $link)) {
            die('Error: ' . mysql_error());
        }
        $this->tancar_bd($link);
    }

    public function deleteDept($id_dept) {
        $link = $this->obrir_bd();
        try {
            mysql_query("SET AUTOCOMMIT=0");
            mysql_query("START TRANSACTION");
            //esborrar empleats d'aquell dept
            $sql = "DELETE FROM emp WHERE id_dept='$id_dept'";
            if (!mysql_query($sql, $link)) {
                die('Error: ' . mysql_error());
            }
            //esborrar dept
            $sql = "DELETE FROM dept WHERE id='$id_dept'";
            if (!mysql_query($sql, $link)) {
                die('Error: ' . mysql_error());
            }
            mysql_query("COMMIT");
        } catch (Exception $e) {
            mysql_query("ROLLBACK");
        }
        $this->tancar_bd($link);
    }

    public function listDepts() {
        $link = $this->obrir_bd();
        $sql = "SELECT * FROM dept";
        $result = mysql_query($sql);
        $i = 0;
        $depts = array();
        while ($row = mysql_fetch_assoc($result)) {
            $depts[$i] = $row;
            $i++;
        }
        $this->tancar_bd($link);
        return $depts;
    }

    public function listDeptsSelect() {
        $link = $this->obrir_bd();
        $sql = "SELECT id,nom FROM dept";
        $result = mysql_query($sql);
        $depts = array();
        while ($row = mysql_fetch_assoc($result)) {
            $depts[$row['id']] = $row['nom'];
        }
        $this->tancar_bd($link);
        return $depts;
    }

    /*     * ******************************* EMPS *********************************** */

    public function getEmp($id_emp) {
        $link = $this->obrir_bd();
        $sql = "SELECT id,nom,cognom,dni,id_dept as departament FROM emp where id='$id_emp'";
        $result = mysql_query($sql);
        $this->tancar_bd($link);
        return mysql_fetch_assoc($result);
    }

    public function addEmp($nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO emp (nom,cognom,dni,id_dept) VALUES ('$nom_e','$cognom_e','$dni_e','$id_d')";
        if (!mysql_query($sql, $link)) {
            die('Error: ' . mysql_error());
        }
        $this->tancar_bd($link);
    }

    public function editEmp($id_e, $nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "UPDATE emp SET nom = '$nom_e' , cognom = '$cognom_e',dni='$dni_e',id_dept='$id_d' WHERE id='$id_e'";
        if (!mysql_query($sql, $link)) {
            die('Error: ' . mysql_error());
        }
        $this->tancar_bd($link);
    }

    public function deleteEmp($id_e) {
        $link = $this->obrir_bd();
        $sql = "DELETE FROM emp WHERE id='$id_e'";
        if (!mysql_query($sql, $link)) {
            die('Error: ' . mysql_error());
        }
        $this->tancar_bd($link);
    }

    public function listEmps() {
        $link = $this->obrir_bd();
        $sql = "SELECT e.id,e.nom,e.cognom,e.dni,d.nom as departament FROM emp e, dept d where e.id_dept=d.id";
        $result = mysql_query($sql);
        $i = 0;
        $emps = array();
        while ($row = mysql_fetch_assoc($result)) {
            $emps[$i] = $row;
            $i++;
        }
        $this->tancar_bd($link);
        return $emps;
    }

}

